
-- **************************************************************************
--    Project Name:   网点客户--目的省份财务表
--    Job Name:       jms_dm.dm_customer_province_bill_amount_dt
--    Author :        modify jixk
--    date：          2023/06/15
-- **************************************************************************
-- **************************************************************************

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions=1000;
set hive.exec.max.dynamic.partitions.pernode=1000;

----网点客户运单相关指标

with yl_oms_oms_waybill_cust as (
        select
         cc.pick_network_code                             --网点编码
        ,cc.pick_network_name                             --网点名称
        ,bb.subordinate_agent_code as pick_finance_code   --财务
        ,bb.subordinate_agent_name as pick_finance_name   --财务
        ,cc.input_time                                    --日期
        ,cc.customer_code                                 --客户Code
        ,cc.customer_name                                 --客户名称
        ,cc.receiver_province_id as receiver_province_id  --目的省份ID
        ,dd.provider_desc receiver_province_name          --目的省份名称
        ,cc.total_bill_count                              --总票数
        ,cc.total_weight_count                            --总重量（内部计费总量）
        ,cc.total_average_weight                          --均重（总重量/总票数）
        ,cc.total_freight                                 --收入（总运费）
        ,cc.total_with_freight
        ,cc.total_without_freight
        ,cc.zero_bill_count
        ,cc.zero_weight_count
        ,cc.zero_average_weight
        ,cc.zero_freight_count
        ,cc.zero_five_bill_count
        ,cc.zero_five_weight_count
        ,cc.zero_five_average_weight
        ,cc.zero_five_freight_count
        ,cc.three_two_bill_count
        ,cc.three_two_weight_count
        ,cc.three_two_average_weight
        ,cc.three_two_freight_count
        ,cc.five_two_bill_count
        ,cc.five_two_weight_count
        ,cc.five_two_average_weight
        ,cc.five_two_freight_count
        ,cc.ten_three_bill_count
        ,cc.ten_three_weight_count
        ,cc.ten_three_average_weight
        ,cc.ten_three_freight_count
        ,cc.bigger_ten_three_bill_count
        ,cc.bigger_ten_three_weight_count
        ,cc.bigger_ten_three_average_weight
        ,cc.bigger_ten_three_freight_count
        ,cc.zerofive_one_bill_count                        --add jixk 2023-04-07
        ,cc.zerofive_one_weight_count                      --add jixk 2023-04-07
        ,cc.zerofive_one_average_weight                    --add jixk 2023-04-07
        ,cc.zerofive_one_freight_count                     --add jixk 2023-04-07
        ,cc.one_two_bill_count                               --add jixk 2023-04-07
        ,cc.one_two_weight_count                           --add jixk 2023-04-07
        ,cc.one_two_average_weight                           --add jixk 2023-04-07
        ,cc.one_two_freight_count                           --add jixk 2023-04-07
        ,cc.two_three_bill_count                           --add jixk 2023-04-07
        ,cc.two_three_weight_count                           --add jixk 2023-04-07
        ,cc.two_three_average_weight                       --add jixk 2023-04-07
        ,cc.two_three_freight_count                        --add jixk 2023-04-07
        ,cc.five_twenty_bill_count                           --add jixk 2023-04-07
        ,cc.five_twenty_weight_count                       --add jixk 2023-04-07
        ,cc.five_twenty_average_weight                       --add jixk 2023-04-07
        ,cc.five_twenty_freight_count                      --add jixk 2023-04-07
        ,cc.over_twenty_bill_count                           --add jixk 2023-04-07
        ,cc.over_twenty_weight_count                       --add jixk 2023-04-07
        ,cc.over_twenty_average_weight                       --add jixk 2023-04-07
        ,cc.over_twenty_freight_count                      --add jixk 2023-04-07
        ,bb.first_franchisee_code as first_franchisee_code --所属加盟商编码
        ,bb.first_franchisee_name as first_franchisee_name --所属加盟商名称
        from
        (select
         aa.pick_network_code as pick_network_code --网点编码
        ,aa.pick_network_name as pick_network_name --网点名称
        ,to_date(input_time) as input_time          --日期
        ,aa.customer_code as customer_code  --客户Code
        ,aa.customer_name as customer_name --客户名称
        ,aa.receiver_province_id as receiver_province_id --目的省份ID
        ,count(aa.waybill_no) as  total_bill_count --总票数
        ,sum(aa.package_inside_charge_weight) as total_weight_count --总重量（内部计费总量）
        ,sum(aa.package_inside_charge_weight)/count(aa.waybill_no) as total_average_weight --均重（总重量/总票数）
        ,sum(aa.total_freight) as total_freight --收入（总运费）
        ,count(if(aa.package_inside_charge_weight=0,aa.waybill_no,null)) as zero_bill_count
        ,sum(if(aa.package_inside_charge_weight=0,aa.package_inside_charge_weight,0)) as zero_weight_count
        ,sum(if(aa.package_inside_charge_weight=0,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight=0,aa.waybill_no,null)) as zero_average_weight
        ,sum(if(aa.package_inside_charge_weight=0,aa.total_freight,0)) as zero_freight_count
        ,count(if(aa.package_inside_charge_weight>0 and aa.package_inside_charge_weight<=0.5,aa.waybill_no,null)) as zero_five_bill_count
        ,sum(if(aa.package_inside_charge_weight>0 and aa.package_inside_charge_weight<=0.5,aa.package_inside_charge_weight,0)) as zero_five_weight_count
        ,sum(if(aa.package_inside_charge_weight>0 and aa.package_inside_charge_weight<=0.5,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>0 and aa.package_inside_charge_weight<=0.5,aa.waybill_no,null)) as zero_five_average_weight
        ,sum(if(aa.package_inside_charge_weight>0 and aa.package_inside_charge_weight<=0.5,aa.total_freight,0)) as zero_five_freight_count
        ,count(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=3.2,aa.waybill_no,null)) as three_two_bill_count
        ,sum(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=3.2,aa.package_inside_charge_weight,0)) as three_two_weight_count
        ,sum(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=3.2,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=3.2,aa.waybill_no,null)) as three_two_average_weight
        ,sum(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=3.2,aa.total_freight,0)) as three_two_freight_count
        ,count(if(aa.package_inside_charge_weight>3.2 and aa.package_inside_charge_weight<=5.2,aa.waybill_no,null)) as five_two_bill_count
        ,sum(if(aa.package_inside_charge_weight>3.2 and aa.package_inside_charge_weight<=5.2,aa.package_inside_charge_weight,0)) as five_two_weight_count
        ,sum(if(aa.package_inside_charge_weight>3.2 and aa.package_inside_charge_weight<=5.2,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>3.2 and aa.package_inside_charge_weight<=5.2,aa.waybill_no,null)) as five_two_average_weight
        ,sum(if(aa.package_inside_charge_weight>3.2 and aa.package_inside_charge_weight<=5.2,aa.total_freight,0)) as five_two_freight_count
        ,count(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=10.3,aa.waybill_no,null)) as ten_three_bill_count
        ,sum(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=10.3,aa.package_inside_charge_weight,0)) as ten_three_weight_count
        ,sum(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=10.3,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=10.3,aa.waybill_no,null)) as ten_three_average_weight
        ,sum(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=10.3,aa.total_freight,0)) as ten_three_freight_count
        ,count(if(aa.package_inside_charge_weight>10.3,aa.waybill_no,null)) as bigger_ten_three_bill_count
        ,sum(if(aa.package_inside_charge_weight>10.3,aa.package_inside_charge_weight,0)) as bigger_ten_three_weight_count
        ,sum(if(aa.package_inside_charge_weight>10.3,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>10.3,aa.waybill_no,null)) as bigger_ten_three_average_weight
        ,sum(if(aa.package_inside_charge_weight>10.3,aa.total_freight,0)) as bigger_ten_three_freight_count
        ,count(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=1,aa.waybill_no,null)) as zerofive_one_bill_count
        ,sum(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=1,aa.package_inside_charge_weight,0)) as zerofive_one_weight_count
        ,sum(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=1,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>0 and aa.package_inside_charge_weight<=0.5,aa.waybill_no,null)) as zerofive_one_average_weight
        ,sum(if(aa.package_inside_charge_weight>0.5 and aa.package_inside_charge_weight<=1,aa.total_freight,0)) as zerofive_one_freight_count
        ,count(if(aa.package_inside_charge_weight>1 and aa.package_inside_charge_weight<=2,aa.waybill_no,null)) as one_two_bill_count
        ,sum(if(aa.package_inside_charge_weight>1 and aa.package_inside_charge_weight<=2,aa.package_inside_charge_weight,0)) as one_two_weight_count
        ,sum(if(aa.package_inside_charge_weight>1 and aa.package_inside_charge_weight<=2,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>1 and aa.package_inside_charge_weight<=2,aa.waybill_no,null)) as one_two_average_weight
        ,sum(if(aa.package_inside_charge_weight>1 and aa.package_inside_charge_weight<=2,aa.total_freight,0)) as one_two_freight_count
         ,count(if(aa.package_inside_charge_weight>2 and aa.package_inside_charge_weight<=3.2,aa.waybill_no,null)) as two_three_bill_count
         ,sum(if(aa.package_inside_charge_weight>2 and aa.package_inside_charge_weight<=3.2,aa.package_inside_charge_weight,0)) as two_three_weight_count
         ,sum(if(aa.package_inside_charge_weight>2 and aa.package_inside_charge_weight<=3.2,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>2 and aa.package_inside_charge_weight<=3.2,aa.waybill_no,null)) as two_three_average_weight
         ,sum(if(aa.package_inside_charge_weight>2 and aa.package_inside_charge_weight<=3.2,aa.total_freight,0)) as two_three_freight_count
         ,count(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=20,aa.waybill_no,null)) as five_twenty_bill_count
         ,sum(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=20,aa.package_inside_charge_weight,0)) as five_twenty_weight_count
         ,sum(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=20,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>5 and aa.package_inside_charge_weight<=20,aa.waybill_no,null)) as five_twenty_average_weight
         ,sum(if(aa.package_inside_charge_weight>5.2 and aa.package_inside_charge_weight<=20,aa.total_freight,0)) as five_twenty_freight_count
         ,count(if(aa.package_inside_charge_weight>20,aa.waybill_no,null)) as over_twenty_bill_count
         ,sum(if(aa.package_inside_charge_weight>20,aa.package_inside_charge_weight,0)) as over_twenty_weight_count
         ,sum(if(aa.package_inside_charge_weight>20,aa.package_inside_charge_weight,0))/count(if(aa.package_inside_charge_weight>20,aa.waybill_no,null)) as over_twenty_average_weight
         ,sum(if(aa.package_inside_charge_weight>20,aa.total_freight,0)) as over_twenty_freight_count
         ,sum(if(total_freight<>0,1,0)) as total_with_freight
         ,sum(if(total_freight=0,1,0)) as total_without_freight
         from
         (select
             input_time,                         --录入时间
             pick_network_code,                  --网点编码
             pick_network_name,                  --网点名称
             customer_code,
             customer_name,
             waybill_no,
             receiver_province_id,
             nvl(package_inside_charge_weight,0) as package_inside_charge_weight,
             nvl(total_freight,0) as total_freight
             FROM jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
             where dt >= date_add('{{ execution_date | cst_ds }}',-67) and dt <= '{{ execution_date | cst_ds }}'
             and date(input_time)>=date_add('{{ execution_date | cst_ds }}',-60)  and date(input_time) <= '{{ execution_date | cst_ds }}'
         ) aa
         group BY
          aa.pick_network_code --网点编码
         ,aa.pick_network_name --网点名称
         ,to_date(input_time)        --日期
         ,aa.customer_code  --客户Code
         ,aa.customer_name --客户名称
         ,aa.receiver_province_id --目的省份ID
         ) cc
         left join jms_dim.dim_lmdm_sys_network_expand bb on cc.pick_network_code=bb.code
         left join (select provider_id,provider_desc from jms_dim.dim_lmdm_sys_network_expand group by provider_id,provider_desc) dd on cc.receiver_province_id=dd.provider_id
         ),

---客户等级
yl_oms_oms_cust_lvl as (
        select customer_code
               ,input_time
               ,pick_network_code
               ,case when sum(total_bill_count)>5000 then 'A'
                     when sum(total_bill_count)>1000 and sum(total_bill_count) <=5000 then 'B'
                     when sum(total_bill_count)>200 and sum(total_bill_count) <=1000 then  'C'
                     when sum(total_bill_count)>50 and sum(total_bill_count) <=200 then 'D'
                     when sum(total_bill_count)>=0 and sum(total_bill_count) <=50 then 'E'
                end as cust_level
          from yl_oms_oms_waybill_cust
          GROUP BY customer_code,input_time,pick_network_code
          ) ,

spmi_composite_bill_cust as (
         select
          aa.send_network_code                                 --网点code
         ,aa.customer_code                                     --客户code
         ,aa.receiver_province_id                              --目的省份
         ,date(aa.waybill_time) as input_time
         ,sum(aa.transfer_fee) as total_transfer_fee           --中转费
         ,sum(aa.operation_fee) as total_operation_fee         --操作费
         ,sum(aa.base_dispatch_fee) as total_base_dispatch_fee --基础派费
         ,sum(aa.piece_fee) as  total_piece_fee                --续重派费
         ,sum(aa.sales_price) as total_sales_price             --面单费
         ,sum(aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee) as prime_cost                             --成本
         ,sum(if(aa.cost_weight=0,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as zero_prime_cost --W=0成本
         ,sum(if(aa.cost_weight>0 and aa.cost_weight<=0.5,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as zero_five_prime_cost --W<=0.5成本
         ,sum(if(aa.cost_weight>0.5 and aa.cost_weight<=3.2,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as three_prime_cost --W<=3.2成本
         ,sum(if(aa.cost_weight>3.2 and aa.cost_weight<=5.2,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as five_prime_cost --W<=5.2成本
         ,sum(if(aa.cost_weight>5.2 and aa.cost_weight<=10.3,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as ten_prime_cost --W<=10.3成本
         ,sum(if(aa.cost_weight>10.3,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as over_ten_prime_cost --W>10.3成本
         ,sum(if(aa.cost_weight>0.5 and aa.cost_weight<=1,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as zerofive_one_prime_cost     --0.5<W<=1成本 add jixk
         ,sum(if(aa.cost_weight>1 and aa.cost_weight<=2,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as one_two_prime_cost                  --1<W<=2成本 add jixk
         ,sum(if(aa.cost_weight>2 and aa.cost_weight<=3.2,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as two_three_prime_cost        --2<W<=3.2成本 add jixk
         ,sum(if(aa.cost_weight>5.2 and aa.cost_weight<=20,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as five_twenty_prime_cost     --5.2<W<=20成本 add jixk
         ,sum(if(aa.cost_weight>20,aa.piece_fee+transfer_fee+aa.operation_fee+aa.sales_price+aa.base_dispatch_fee,0)) as over_twenty_prime_cost                             --20<W成本 add jixk
         from
            (select send_network_code
                    ,customer_code
                    ,receiver_province_id
                    ,waybill_time
                    ,cost_weight
                    ,transfer_fee
                    ,operation_fee
                    ,base_dispatch_fee
                    ,piece_fee
                    ,sales_price
                from (select send_network_code
                             ,customer_code
                             ,receiver_province_id
                             ,waybill_time
                             ,cost_weight
                             ,if(transfer_fee is null or transfer_fee='',0,transfer_fee) as transfer_fee
                             ,if(operation_fee is null or operation_fee='',0,operation_fee) as operation_fee
                             ,if(base_dispatch_fee is null or base_dispatch_fee='',0,base_dispatch_fee) as base_dispatch_fee
                             ,if(piece_fee is null or piece_fee='',0,piece_fee) as piece_fee
                             ,if(sales_price is null or transfer_fee='',0,sales_price) as sales_price
                             ,row_number() over(partition by waybill_no order by dt desc) rnk
                        from spmi_ods.spmi_composite_bill
                       where dt >= date_add('{{ execution_date | cst_ds }}',-60) and dt <= '{{ execution_date | cst_ds }}'
                         and date(waybill_time)>=date_add('{{ execution_date | cst_ds }}',-60) and date(waybill_time) <= '{{ execution_date | cst_ds }}'
                      ) t where t.rnk=1
            ) aa
         group by aa.send_network_code
                  ,aa.customer_code         --客户code
                  ,aa.receiver_province_id  --目的省份
                  ,date(aa.waybill_time)
         ),

yl_oms_pick_bill_cust as (
         select pick_network_code
                ,customer_code
                ,receiver_province_id
                ,date(input_time) as input_time
                ,sum(bill_amount) as total_bill_amount
                ,sum(if(charge_weight=0,bill_amount,0)) as zero_bill_amount --W=0成本
                ,sum(if(charge_weight>0 and charge_weight<=0.5,bill_amount,0)) as zero_five_bill_amount --W<=0.5成本
                ,sum(if(charge_weight>0.5 and charge_weight<=3.2,bill_amount,0)) as three_bill_amount --W<=3.2成本
                ,sum(if(charge_weight>3.2 and charge_weight<=5.2,bill_amount,0)) as five_bill_amount --W<=5.2成本
                ,sum(if(charge_weight>5.2 and charge_weight<=10.3,bill_amount,0)) as ten_bill_amount --W<=10.3成本
                ,sum(if(charge_weight>10.3,bill_amount,0)) as over_ten_bill_amount --W>10.3成本
                ,sum(if(charge_weight>0.5 and charge_weight<=1,bill_amount,0)) as zerofive_one_bill_amount  --0.5<W<=1成本 add jixk
                ,sum(if(charge_weight>1 and charge_weight<=2,bill_amount,0)) as one_two_bill_amount    --1<W<=2成本 add jixk
                ,sum(if(charge_weight>2 and charge_weight<=3.2,bill_amount,0)) as two_three_bill_amount  --2<W<=3.2成本 add jixk
                ,sum(if(charge_weight>5.2 and charge_weight<=20,bill_amount,0)) as five_twenty_bill_amount --5.2<W<=20成本 add jixk
                ,sum(if(charge_weight>20,bill_amount,0)) as over_twenty_bill_amount                          --20<W成本 add jixk
           from (select pick_network_code
                        ,customer_code
                        ,receiver_province_id
                        ,input_time
                        ,bill_amount
                        ,charge_weight
                   from (select pick_network_code
                                ,customer_code
                                ,receiver_province_id
                                ,input_time
                                ,nvl(bill_amount,0) as bill_amount
                                ,nvl(charge_weight,0) as charge_weight
                                ,row_number() over (partition by waybill_no order by dt desc) as rnk
                                from
                                jms_dwd.dwd_yl_oms_pick_bill_base_dt
                                where dt >= date_add('{{ execution_date | cst_ds }}',-60) and dt <= '{{ execution_date | cst_ds }}'
                                and date(input_time)>= date_add('{{ execution_date | cst_ds }}',-60) and date(input_time) <= '{{ execution_date | cst_ds }}'
                                and fee_type_code='S0510'
                        ) where rnk =1
                ) aa
          group by pick_network_code,receiver_province_id,customer_code,date(input_time)
)

insert overwrite table jms_dm.dm_customer_province_bill_amount_dt partition (dt)
select t1.pick_network_code as pick_network_code --网点编码
       ,pick_network_name --网点名称
       ,first_franchisee_code --所属加盟商编码
       ,first_franchisee_name --所属加盟商名称
       ,pick_finance_code --所属财务中心编码
       ,pick_finance_name --所属财务中心名称
       ,t1.input_time --汇总日期
       ,t1.customer_code --客户Code
       ,t1.customer_name --客户name
       ,receiver_province_id --目的省份ID
       ,receiver_province_name --目的省份
       ,total_bill_count --总票数
       ,total_weight_count --总重量（内部计费总量）
       ,total_average_weight --均重（总重量/总票数）
       ,total_freight --收入（总运费）
       ,total_transfer_fee --中转费
       ,total_operation_fee --操作费
       ,total_base_dispatch_fee --基础派费
       ,total_piece_fee --续重派费
       ,total_sales_price --面单费
       ,total_bill_amount --取件提成
       ,total_transfer_fee+total_operation_fee+total_base_dispatch_fee+total_piece_fee+total_sales_price+total_bill_amount  as total_prime_cost --成本
       ,total_freight-(total_transfer_fee+total_operation_fee+total_base_dispatch_fee+total_piece_fee+total_sales_price+total_bill_amount) as total_profit --利润
       ,(total_freight-(total_transfer_fee+total_operation_fee+total_base_dispatch_fee+total_piece_fee+total_sales_price+total_bill_amount))/total_bill_count as total_average_profit --平均利润
       ,(total_transfer_fee+total_operation_fee+total_base_dispatch_fee+total_piece_fee+total_sales_price+total_bill_amount)/total_bill_count as total_average_prime --平均成本
       ,zero_bill_count
       ,zero_weight
       ,zero_average_weight
       ,zero_freight
       ,zero_prime_cost+zero_bill_amount as zero_prime_cost
       ,zero_freight-zero_prime_cost-zero_bill_amount as zero_profit
       ,(zero_freight-zero_prime_cost-zero_bill_amount)/nvl(zero_bill_count,1) as zero_average_profit
       ,one_bill_count
       ,one_weight
       ,one_average_weight
       ,one_freight
       ,one_prime_cost+one_bill_amount as one_prime_cost
       ,one_freight-one_prime_cost-one_bill_amount as one_profit
       ,(one_freight-one_prime_cost-one_bill_amount)/nvl(one_bill_count,1) as one_average_profit
       ,two_bill_count
       ,two_weight
       ,two_average_weight
       ,two_freight
       ,two_prime_cost+two_bill_amount as two_prime_cost
       ,two_freight-two_prime_cost-two_bill_amount as two_profit
       ,(two_freight-two_prime_cost-two_bill_amount)/nvl(two_bill_count,1) as two_average_profit
       ,three_bill_count
       ,three_weight
       ,three_average_weight
       ,three_freight
       ,three_prime_cost+three_bill_amount as three_prime_cost
       ,three_freight-three_prime_cost-three_bill_amount as three_profit
       ,(three_freight-three_prime_cost-three_bill_amount)/nvl(three_bill_count,1) as three_average_profit
       ,four_bill_count
       ,four_weight
       ,four_average_weight
       ,four_freight
       ,four_prime_cost+four_bill_amount as four_prime_cost
       ,four_freight-four_prime_cost-four_bill_amount as four_profit
       ,(four_freight-four_prime_cost-four_bill_amount)/nvl(four_bill_count,1) as four_average_profit
       ,five_bill_count
       ,five_weight
       ,five_average_weight
       ,five_freight
       ,five_prime_cost+five_bill_amount as five_prime_cost
       ,five_freight-five_prime_cost-five_bill_amount as five_profit
       ,(five_freight-five_prime_cost-five_bill_amount)/nvl(five_bill_count,1) as five_average_profit
       ,six_bill_count
       ,six_weight
       ,six_average_weight
       ,six_freight
       ,six_prime_cost+six_bill_amount as six_prime_cost
       ,six_freight-six_prime_cost-six_bill_amount as six_profit
       ,(six_freight-six_prime_cost-six_bill_amount)/nvl(six_bill_count,1) as six_average_profit
       ,seven_bill_count
       ,seven_weight
       ,seven_average_weight
       ,seven_freight
       ,seven_prime_cost+seven_bill_amount as seven_prime_cost
       ,seven_freight-seven_prime_cost-seven_bill_amount as seven_profit
       ,(seven_freight-seven_prime_cost-seven_bill_amount)/nvl(seven_bill_count,1) as seven_average_profit
       ,eight_bill_count
       ,eight_weight
       ,eight_average_weight
       ,eight_freight
       ,eight_prime_cost+eight_bill_amount as eight_prime_cost
       ,eight_freight-eight_prime_cost-eight_bill_amount as eight_profit
       ,(eight_freight-eight_prime_cost-eight_bill_amount)/nvl(eight_bill_count,1) as eight_average_profit
       ,nine_bill_count
       ,nine_weight
       ,nine_average_weight
       ,nine_freight
       ,nine_prime_cost+nine_bill_amount as nine_prime_cost
       ,nine_freight-nine_prime_cost-nine_bill_amount as nine_profit
       ,(nine_freight-nine_prime_cost-nine_bill_amount)/nvl(nine_bill_count,1) as nine_average_profit
       ,ten_bill_count
       ,ten_weight
       ,ten_average_weight
       ,ten_freight
       ,ten_prime_cost+ten_bill_amount as ten_prime_cost
       ,ten_freight-ten_prime_cost-ten_bill_amount as ten_profit
       ,(ten_freight-ten_prime_cost-ten_bill_amount)/nvl(ten_bill_count,1) as ten_average_profit
       ,if_in_province_bill
       ,total_with_freight
       ,total_without_freight
       ,t2.cust_level
       ,t1.input_time as dt
  FROM (select yoow.pick_network_code                  as pick_network_code --网点编码
               ,yoow.pick_network_name                          --网点名称
               ,yoow.first_franchisee_code                      --所属加盟商编码
               ,yoow.first_franchisee_name                      --所属加盟商名称
               ,yoow.pick_finance_code                          --所属财务中心编码
               ,yoow.pick_finance_name                          --所属财务中心名称
               ,yoow.input_time                                 --汇总日期
               ,yoow.customer_code                              --客户Code
               ,yoow.customer_name                              --客户name
               ,yoow.receiver_province_id                       --目的省份ID
               ,yoow.receiver_province_name                     --目的省份
               ,yoow.total_bill_count                           --总票数
               ,yoow.total_with_freight
               ,yoow.total_without_freight
               ,nvl(yoow.total_weight_count,0)                  as total_weight_count --总重量（内部计费总量）
               ,nvl(yoow.total_average_weight,0)                as total_average_weight --均重（总重量/总票数）
               ,nvl(yoow.total_freight,0)                       as total_freight --收入（总运费）
               ,nvl(scb.total_transfer_fee,0)                   as total_transfer_fee--中转费
               ,nvl(scb.total_operation_fee,0)                  as total_operation_fee--操作费
               ,nvl(scb.total_base_dispatch_fee,0)              as total_base_dispatch_fee--基础派费
               ,nvl(scb.total_piece_fee,0)        as total_piece_fee--续重派费
               ,nvl(scb.total_sales_price,0)      as total_sales_price --面单费
               ,nvl(yopb.total_bill_amount,0)     as total_bill_amount--取件提成
               ,yoow.zero_bill_count              as zero_bill_count
               ,nvl(yoow.zero_weight_count,0)     as  zero_weight
               ,nvl(yoow.zero_average_weight,0)   as zero_average_weight
               ,nvl(yoow.zero_freight_count,0)    as zero_freight
               ,nvl(scb.zero_prime_cost,0)        as zero_prime_cost
               ,nvl(yopb.zero_bill_amount,0)      as zero_bill_amount
               ,yoow.zero_five_bill_count         as one_bill_count
               ,nvl(yoow.zero_five_weight_count,0)   as one_weight
               ,nvl(yoow.zero_five_average_weight,0) as one_average_weight
               ,nvl(yoow.zero_five_freight_count,0)  as one_freight
               ,nvl(scb.zero_five_prime_cost,0)      as one_prime_cost
               ,nvl(yopb.zero_five_bill_amount,0)    as one_bill_amount
               ,yoow.three_two_bill_count as two_bill_count
               ,nvl(yoow.three_two_weight_count,0) as two_weight
               ,nvl(yoow.three_two_average_weight,0) as two_average_weight
               ,nvl(yoow.three_two_freight_count,0) as two_freight
               ,nvl(scb.three_prime_cost,0) as two_prime_cost
               ,nvl(yopb.three_bill_amount,0) as two_bill_amount
               ,yoow.five_two_bill_count as three_bill_count
               ,nvl(yoow.five_two_weight_count,0) as three_weight
               ,nvl(yoow.five_two_average_weight,0) as three_average_weight
               ,nvl(yoow.five_two_freight_count,0) as three_freight
               ,nvl(scb.five_prime_cost,0) as three_prime_cost
               ,nvl(yopb.five_bill_amount,0) as three_bill_amount
               ,yoow.ten_three_bill_count as four_bill_count
               ,nvl(yoow.ten_three_weight_count,0) as four_weight
               ,nvl(yoow.ten_three_average_weight,0) as four_average_weight
               ,nvl(yoow.ten_three_freight_count,0) as four_freight
               ,nvl(scb.ten_prime_cost,0) as four_prime_cost
               ,nvl(yopb.ten_bill_amount,0) as four_bill_amount
               ,yoow.bigger_ten_three_bill_count as five_bill_count
               ,nvl(yoow.bigger_ten_three_weight_count,0) as five_weight
               ,nvl(yoow.bigger_ten_three_average_weight,0) as five_average_weight
               ,nvl(yoow.bigger_ten_three_freight_count,0) as five_freight
               ,nvl(scb.over_ten_prime_cost,0) as five_prime_cost
               ,nvl(yopb.over_ten_bill_amount,0) as five_bill_amount
               ,yoow.zerofive_one_bill_count as six_bill_count                    --add jixk
               ,nvl(yoow.zerofive_one_weight_count,0) as six_weight
               ,nvl(yoow.zerofive_one_average_weight,0) as six_average_weight
               ,nvl(yoow.zerofive_one_freight_count,0) as six_freight
               ,nvl(scb.zerofive_one_prime_cost,0) as six_prime_cost
               ,nvl(yopb.zerofive_one_bill_amount,0) as six_bill_amount
               ,yoow.one_two_bill_count as seven_bill_count                       --add jixk
               ,nvl(yoow.one_two_weight_count,0) as seven_weight
               ,nvl(yoow.one_two_average_weight,0) as seven_average_weight
               ,nvl(yoow.one_two_freight_count,0) as seven_freight
               ,nvl(scb.one_two_prime_cost,0) as seven_prime_cost
               ,nvl(yopb.one_two_bill_amount,0) as seven_bill_amount
               ,yoow.two_three_bill_count as eight_bill_count                     --add jixk
               ,nvl(yoow.two_three_weight_count,0) as eight_weight
               ,nvl(yoow.two_three_average_weight,0) as eight_average_weight
               ,nvl(yoow.two_three_freight_count,0) as eight_freight
               ,nvl(scb.two_three_prime_cost,0) as eight_prime_cost
               ,nvl(yopb.two_three_bill_amount,0) as eight_bill_amount
               ,yoow.five_twenty_bill_count as nine_bill_count                    --add jixk
               ,nvl(yoow.five_twenty_weight_count,0) as nine_weight
               ,nvl(yoow.five_twenty_average_weight,0) as nine_average_weight
               ,nvl(yoow.five_twenty_freight_count,0) as nine_freight
               ,nvl(scb.five_twenty_prime_cost,0) as nine_prime_cost
               ,nvl(yopb.five_twenty_bill_amount,0) as nine_bill_amount
               ,yoow.over_twenty_bill_count as ten_bill_count                     --add jixk
               ,nvl(yoow.over_twenty_weight_count,0) as ten_weight
               ,nvl(yoow.over_twenty_average_weight,0) as ten_average_weight
               ,nvl(yoow.over_twenty_freight_count,0) as ten_freight
               ,nvl(scb.over_twenty_prime_cost,0) as ten_prime_cost
               ,nvl(yopb.over_twenty_bill_amount,0) as ten_bill_amount
               ,if(cast(network.provider_id as string) = nvl(cast(yoow.receiver_province_id as string),-999),1,0) as if_in_province_bill
         from yl_oms_oms_waybill_cust yoow
         left join spmi_composite_bill_cust scb
           on yoow.pick_network_code = scb.send_network_code
          and nvl(yoow.customer_code,99) =nvl(scb.customer_code,99)
          and yoow.receiver_province_id = scb.receiver_province_id
          and yoow.input_time=scb.input_time
         left join yl_oms_pick_bill_cust yopb
           on yoow.pick_network_code = yopb.pick_network_code
          and nvl(yoow.customer_code,99) =nvl(yopb.customer_code,99)
          and yoow.receiver_province_id = yopb.receiver_province_id
          and yoow.input_time=yopb.input_time
         left join jms_dim.dim_network_whole_massage network
           on yoow.pick_network_code = network.code
        ) t1
    left join yl_oms_oms_cust_lvl t2
      on t1.customer_code = t2.customer_code
     and t1.input_time= t2.input_time
     and t1.pick_network_code = t2.pick_network_code
distribute by dt,pmod(hash(rand()),10)
;